﻿using Dapper;
using SIG.DAL.Dapper.Model;
using SIG.Infrastructure.Helper;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIG.DAL.Dapper.Repositories
{
    public class UserRepository
    {
        private DbConnection _connection;
        public User SignIn(string username, string password)
        {
            User user;
            using (_connection = Utilities.GetOpenConnection())
            {
                user = _connection.GetList<User>(new { UserName = username }).FirstOrDefault();
                if (user != null)
                {
                    byte[] salt = Convert.FromBase64String(user.SecurityStamp);
                    string pwdHash = Hash.HashPasswordWithSalt(password, salt);
                    if (user.PasswordHash == pwdHash)
                    {
                        //  _logger.Info(string.Format(Logs.UserLoginMessage, user.UserName));
                        return user;
                    }
                    else
                        return null;
                }
               // result = _connection.Get<Album>(id);
            }
            return null;            
        }

        public bool IsExistEmail(string email)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {                
                result = _connection.RecordCount<User>("Where Email = @Email", new { Email = email });
            }
            return result > 0;
        }
        public bool IsExistEmail(string email, Guid id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {               
                result = _connection.RecordCount<User>("Where Email = @Email and Id != @Id", new { Email = email, Id = id });
            }
            return result > 0;
        }

        public bool IsExistUserName(string userName)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.RecordCount<User>("Where UserName = @UserName", new { UserName = userName });
            }
            return result > 0;
        }

       


        public int Register(string userName, string email, string password, string realName)
        {          
            if (IsExistEmail(email))
            {
                return 1; //1 邮箱已存在
            }            
            if (IsExistUserName(userName))
            {
                return 2; //1 用户名已存在
            }


            var securityStamp = Hash.GenerateSalt();
            var passwordHash = Hash.HashPasswordWithSalt(password, securityStamp);

            var newUser = new User()
            {
               
                Id = IdentityGenerator.SequentialGuid(),        
                UserName = userName,
                RealName = realName,
                Email = email,
                SecurityStamp = Convert.ToBase64String(securityStamp),
                PasswordHash = passwordHash,
                CreateDate = DateTime.Now,
                IsActive = true
            };

            //_logger.Info(string.Format(Logs.CreateMessage, newUser.EntityName, userName));
            Create(newUser);

            // SetUserCookies(false, newUser);

            return 0;

        }
        public User GetById(Guid id)
        {
            User result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<User>(id);
            }
            return result;
        }
        public void Create(User model)
        {         
            using (_connection = Utilities.GetOpenConnection())
            {
                // _connection.Insert(model);
                string query = "INSERT INTO Users(Id,UserName,Email,PasswordHash,SecurityStamp,RealName,IsActive," +
                    "CreateDate)VALUES(@Id,@UserName,@Email,@PasswordHash,@SecurityStamp,@RealName,@IsActive," +
                    "@CreateDate)";
                _connection.Execute(query, new { Id = model.Id, UserName = model.UserName,
                    Email = model.Email, PasswordHash = model.PasswordHash,
                    SecurityStamp = model.SecurityStamp, RealName = model.RealName,
                    IsActive = model.IsActive,
                    CreateDate = model.CreateDate });
            }          
        }

        public int Update(User model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Delete(User model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(Guid id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<User>(id);
            }
            return result;
        }



        public IEnumerable<User> GetListPagedItems(int pageIndex, int pageSize, int? parentId, string keyword, DateTime? startDate, DateTime? endDate, out int totalCount)
        {
            IEnumerable<User> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                var sqlCount = "SELECT COUNT(*) AS Expr1 FROM " +
                    "(SELECT DISTINCT  Users.* FROM UserRoles Right JOIN  Users ON UserRoles.UserId = Users.Id WHERE 1=1 ";

                var sql = "SELECT  * from (SELECT " +
                    "* , ROW_NUMBER() OVER(ORDER BY CreateDate DESC) AS RowNum FROM(SELECT DISTINCT  Users.* " +
                    " FROM UserRoles Right JOIN Users ON " +
                     " UserRoles.UserId = Users.Id WHERE 1 = 1 ";

                if (parentId > 0)
                {
                    sqlCount = sqlCount + " and UserRoles.RoleId = @RoleId ";
                    sql = sql + " and UserRoles.RoleId = @RoleId ";
                }
                if (!string.IsNullOrEmpty(keyword))
                {
                    keyword = keyword.Replace("[", "[[]").Replace("%", "[%]");
                    keyword = "%" + keyword + "%";

                    sqlCount = sqlCount + " and (Users.UserName like @Keyword or Users.Email like @Keyword)";
                    sql = sql + " and (Users.UserName like @Keyword or Users.Email like @Keyword) ";
                }

                if (startDate != null)
                {
                    sqlCount = sqlCount + " and Users.CreateDate >= @StartDate ";
                    sql = sql + " and Users.CreateDate >= @StartDate ";
                }

                if (endDate != null)
                {
                    sqlCount = sqlCount + " and Users.CreateDate <= @EndDate ";
                    sql = sql + " and Users.CreateDate >= @EndDate ";
                }


                sqlCount = sqlCount + ") AS RESULT";
                sql = sql + ") AS RESULT ) as tblusers WHERE RowNum >= @StartIndex AND RowNum< @EndIndex ORDER BY RowNum";

                totalCount = _connection.ExecuteScalar<int>(sqlCount, new { Keyword = keyword,
                    RoleId = parentId,
                    StartDate = startDate,
                    EndDate = endDate
                });
                result = _connection.Query<User>(sql, new { StartIndex = (pageIndex - 1) * pageSize + 1, EndIndex = pageIndex * pageSize + 1, Keyword = keyword,
                    RoleId = parentId,
                    StartDate = startDate,
                    EndDate = endDate
                }).ToList();

            }
            return result;

        }

        public void SetUserRoles(Guid userId, int[] roleId)
        {
            using (_connection = Utilities.GetOpenConnection())
            {
                //result = _connection.Insert(model);
                string deleteQuery = "DELETE FROM UserRoles WHERE UserId = @UserId";
                _connection.Execute(deleteQuery, new { UserId = userId });

                foreach (var item in roleId)
                {
                    string query = "INSERT INTO UserRoles(RoleId,UserId)VALUES(@RoleId,@UserId)";
                    _connection.Execute(query, new { UserId = userId, RoleId = item });
                }
            }
        }

        /// <summary>
        /// 重设密码
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="password"></param>
        /// <returns></returns>

        public bool SetPassword(Guid userId, string password)

        {
            var user = GetById(userId);
            try
            {

                var securityStamp = Hash.GenerateSalt();
                var pwdHash = Hash.HashPasswordWithSalt(password, securityStamp);

                user.SecurityStamp = Convert.ToBase64String(securityStamp);
                user.PasswordHash = pwdHash;
                Update(user);
                //log 

                //_logger.Info(string.Format(Logs.RestPwdMessage, user.EntityName, user.UserName));
                return true;
            }
            catch (Exception er)
            {
               // var message = String.Format(Logs.ErrorRestPwdMessage, user.EntityName);
               // _logger.Error(message, er);
                return false;
            }



        }


    }
}
